/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Admin;

import Class.Fitness;
import Class.News;
import Connection.ConnectionDB;
import Class.Nutrition;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

/**
 *
 * @author Tomcat
 */
public class InitData {

    static Connection cn = null;
    static Statement st = null;
    static ResultSet rs = null;


    public static TableModel SetTableModel(Vector data , Vector header) {
        TableModel model = new DefaultTableModel(data, header) {
                @Override
                public boolean isCellEditable(int row, int column) {
                    return false;
                }
            };
            return model;
    }
    //Init Data for Fitness Table when user click Fitness Button
    public static Vector InitFitnessTableData(JTable tblFitness) {
        cn = ConnectionDB.Connect();
        Vector header = new Vector();
        Vector data = new Vector();
        header.add("Fitness ID");
        header.add("Fitness Name");
        header.add("Fee");
        header.add("Train Time");
        header.add("Hour/Day");
        header.add("Fitness Content");
        header.add("Fitness Image");
        header.add("Status");
        try {
            String sqlFitness = "SELECT * FROM Fitness";
            st = cn.createStatement();
            rs = st.executeQuery(sqlFitness);
            ResultSetMetaData rsm = rs.getMetaData();
            while (rs.next()) {
                Vector row = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    row.add(rs.getString(i));
                }
                data.add(row);
            }
            tblFitness.setModel(SetTableModel(data, header));
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }
//Add new fitness

    public static int AddFitness(Fitness fitness) {
        int executeUpdate = 0;
        cn = ConnectionDB.Connect();
        try {
            String sqlInsertNutri = "INSERT INTO Fitness([FitnessName],[Fee],[TrainTime],[HourPerDay],[FitnessContent],[FitImage],[Status]) VALUES (?,?,?,?,?,?,?)";
            PreparedStatement ps = cn.prepareStatement(sqlInsertNutri);
            ps.setString(1, fitness.getFitName());
            ps.setString(2, fitness.getFee());
            ps.setString(3, fitness.getTrainPeriod());
            ps.setString(4, fitness.getHour());
            ps.setString(5, fitness.getFitContent());
            ps.setString(6, fitness.getFitImage());
            ps.setString(7, fitness.getFitStatus());
            executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return executeUpdate;

    }
    //Update fitness

    public static int UpdateFitness(Fitness fitness) {
        int resultUpdate = 0;
        cn = ConnectionDB.Connect();
        String idUpdate = fitness.getFitID();
        try {
            String sqlUpdateFitness = "UPDATE Fitness SET FitnessName = ? , Fee = ? , TrainTime = ?, HourPerDay = ?, FitnessContent = ? ,FitImage = ? , Status = ? WHERE FitnessID = ?";
            PreparedStatement ps = cn.prepareStatement(sqlUpdateFitness);
            ps.setString(1, fitness.getFitName());
            ps.setString(2, fitness.getFee());
            ps.setString(3, fitness.getTrainPeriod());
            ps.setString(4, fitness.getHour());
            ps.setString(5, fitness.getFitContent());
            ps.setString(6, fitness.getFitImage());
            ps.setString(7, fitness.getFitStatus());   
            ps.setString(8, idUpdate);
            resultUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultUpdate;
    }

    public static int DeleteFitness(Vector data, JTable tblFitness) {
        int resultDelete = 0;
        int[] selectedRows = tblFitness.getSelectedRows();
        String idArray = "(";
        for (int i = 0; i < selectedRows.length; i++) {
            Vector row = (Vector) data.get(selectedRows[i]);    //Get row which has ID has been deleted
            String ID = row.get(0).toString();                  //Get ID of row
            idArray += ID + ",";
        }
        idArray = idArray.substring(0, idArray.length() - 1);   //Delete char , at last
        idArray += ")";
        String sqlDelete = "DELETE Fitness WHERE FitnessID IN" + idArray;
        try {
            st = cn.createStatement();
            resultDelete = st.executeUpdate(sqlDelete);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultDelete;
    }
    //Init data of a fitness when user click Update button

    public static ResultSet InitContentFitness(String fitnessID) {
        cn = ConnectionDB.Connect();
        String sql = "SELECT * FROM Fitness WHERE FitnessID LIKE '" + fitnessID + "' ";
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public static Fitness[] Search(String keyword) {
        int count = 0;
        int no = 0;
        Connection cn2 = ConnectionDB.Connect();
        cn = ConnectionDB.Connect();
        String sqlSearch = "SELECT * FROM Fitness WHERE FitnessName LIKE N'%" + keyword + "%'";
        Fitness[] fitness = new Fitness[0];
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sqlSearch);
            Statement st2 = cn2.createStatement();
            ResultSet rs2 = st2.executeQuery(sqlSearch);
            while (rs2.next()) {
                count++;    //Count number of results found
            }
            fitness = new Fitness[count];
            if (count > 0) {
                while (rs.next()) {
                    String id = rs.getString(1);
                    String name = rs.getString(2);
                    String fee = rs.getString(3);
                    String period = rs.getString(4);
                    String hour = rs.getString(5);
                    String content = rs.getString(6);
                    String img = rs.getString(7);
                    String status = rs.getString(8);
                    fitness[no] = new Fitness(id, name, fee, period, hour, img, content, status);
                    no++;
                }
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return fitness;
    }

    public static Fitness[] AdvanceSearch(String fitID, String fitName, String fee, String period, String fitMins) {
        cn = ConnectionDB.Connect();
        Connection cn2 = ConnectionDB.Connect();
        Fitness[] fitness = new Fitness[0];
        int count  = 0;
        int no = 0;
        String sqlSearch = "SELECT * FROM Fitness WHERE 1 = 1";
        if (!fitID.trim().equalsIgnoreCase("")) {
            sqlSearch += "AND FitnessID = '" + fitID + "'";
        }
        if (!fitName.trim().equalsIgnoreCase("")) {
            sqlSearch += "AND FitnessName LIKE N'%" + fitName + "'%";
        }
        if (!fee.trim().equalsIgnoreCase("")) {
            sqlSearch += "AND Fee = '" + fee + "'";
        }
        if (!period.trim().equalsIgnoreCase("")) {
            sqlSearch += "AND TrainTime = '" + period + "'";
        }
        if (!fitMins.trim().equalsIgnoreCase("")) {
            sqlSearch += "AND HourPerDay = '" + fitMins + "'";
        }
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sqlSearch);
            Statement st2 = cn2.createStatement();
            ResultSet rs2 = st2.executeQuery(sqlSearch);
            while (rs2.next()) {
                count++;    //Count number of results found
            }
            fitness = new Fitness[count];
            if (count > 0) {
                while (rs.next()) {
                    String id = rs.getString(1);
                    String name = rs.getString(2);
                    String fitFee = rs.getString(3);
                    String fitPeriod = rs.getString(4);
                    String hour = rs.getString(5);
                    String content = rs.getString(6);
                    String img = rs.getString(7);
                    String status = rs.getString(8);
                    fitness[no] = new Fitness(id, name, fitFee, fitPeriod, hour, img, content, status);
                    no++;
                }
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return fitness;
    }
    //------------------------------------Nutrition-----------------------------
    //Init Data for Nutrition Table when user click menu Nutrition

    public static Vector InitNutriTableData(JTable tblNutri) {
        cn = ConnectionDB.Connect();
        Vector header = new Vector();
        Vector data = new Vector();
        header.add("Nutrition ID");
        header.add("Nutrition Name");
        header.add("Nutri Image");
        header.add("Status");
        try {
            String sqlNutri = "SELECT * FROM Nutrition";
            st = cn.createStatement();
            rs = st.executeQuery(sqlNutri);
            while (rs.next()) {
                Vector row = new Vector();
                row.add(rs.getString(1));   //Nutri ID
                row.add(rs.getString(2));   //Nutri Name
                row.add(rs.getString(4));   //Nutri image
                row.add(rs.getString(5));   //Status
                data.add(row);
            }
            tblNutri.setModel(SetTableModel(data, header));
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }
//Method init Data to JTextArea NutriContent when user click one row

    public static ResultSet InitContentNutri(String nutriID) {
        cn = ConnectionDB.Connect();
        String sql = "SELECT * FROM Nutrition WHERE NutriID = '" + nutriID + "'";
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public static int AddNutri(Nutrition nutri) {
        int executeUpdate = 0;
        cn = ConnectionDB.Connect();
        try {
            String sqlInsertNutri = "INSERT INTO Nutrition([NutriName],[NutriContent],[NutriImage],[Status]) VALUES (?,?,?,?)";
            PreparedStatement ps = cn.prepareStatement(sqlInsertNutri);
            ps.setString(1, nutri.getNutriName());
            ps.setString(2, nutri.getNutriContent());
            ps.setString(3, nutri.getNutriImage());
            ps.setString(4, nutri.getNutriStatus());
            executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return executeUpdate;
    }

    public static int DeleteNutri(Vector data, JTable tblNutri) {
        int resultDelete = 0;
        int[] selectedRows = tblNutri.getSelectedRows();
        String idArray = "(";
        for (int i = 0; i < selectedRows.length; i++) {
            Vector row = (Vector) data.get(selectedRows[i]);
            String nutriID = row.get(0).toString();
            idArray += nutriID + ",";
        }
        idArray = idArray.substring(0, idArray.length() - 1);
        idArray += ")";
        String sqlDeleteNutri = "DELETE Nutrition WHERE NutriID IN" + idArray;
        try {
            st = cn.createStatement();
            resultDelete = st.executeUpdate(sqlDeleteNutri);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultDelete;
    }

    public static int UpdateNutri(Nutrition nutri) {
        int resultUpdate = 0;
        cn = ConnectionDB.Connect();
        String idUpdate = nutri.getNutriID();
        try {
            String sqlUpdateNutri = "UPDATE Nutrition SET NutriName = ? , NutriContent = ? , NutriImage = ? , Status = ? WHERE NutriID = ?";
            PreparedStatement ps = cn.prepareStatement(sqlUpdateNutri);
            ps.setString(1, nutri.getNutriName());
            ps.setString(2, nutri.getNutriContent());
            ps.setString(3, nutri.getNutriImage());
            ps.setString(4, nutri.getNutriStatus());
            ps.setInt(5, Integer.parseInt(idUpdate));
            resultUpdate = ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultUpdate;
    }
     public static int DeleteNutriImg(String nutriID) {
        int resultUpdate = 0;
        cn = ConnectionDB.Connect();
        try {
            String sqlUpdateNutri = "UPDATE Nutrition SET NutriImage = ?  WHERE NutriID = ?";
            PreparedStatement ps = cn.prepareStatement(sqlUpdateNutri);
            ps.setString(1, "");
            ps.setString(2, nutriID);
            resultUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultUpdate;
    }
    public static Nutrition[] SearchNutri(String keyword,int searchBy) {
        int count = 0;
        int no = 0;
        cn = ConnectionDB.Connect();

        String sqlSearch = "SELECT * FROM Nutrition WHERE 1 = 1";
        if (searchBy == 0) {
            sqlSearch += "AND NutriID = '"+keyword+"'";
        } else {
            sqlSearch += "AND NutriName LIKE N'%"+keyword+"%'";
        }
        Nutrition[] nutri = new Nutrition[0];
        try {
            st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = st.executeQuery(sqlSearch);
            if (rs.last()) {
                count = rs.getRow();
                rs.beforeFirst();
            }
            nutri = new Nutrition[count];
            if (count > 0) {
                while (rs.next()) {
                    String id = rs.getString(1);
                    String name = rs.getString(2);
                    String content = rs.getString(3);
                    String img = rs.getString(4);
                    String status = rs.getString(5);
                    nutri[no] = new Nutrition(id, name,  content, img, status);
                    no++;
                }
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return nutri;
    }
// ------------------------------------ Customer -------------------------------
    public  static Vector InitCustomerTableData(JTable tbl) {
        cn = ConnectionDB.Connect();
        Vector header = new Vector();
        Vector data = new Vector();
        header.add("Customer ID");
        header.add("Customer Name");
        header.add("Status");
        try {
            String sql = "SELECT * FROM Customer";
            st = cn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Vector row = new Vector();
                row.add(rs.getString(1));
                row.add(rs.getString(2));
                row.add(rs.getString(10));
                data.add(row);
            }
            tbl.setModel(SetTableModel(data, header));
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
    }
     public static ResultSet InitContentCustomer(String ID) {
        cn = ConnectionDB.Connect();
        String sql = "SELECT * FROM Customer WHERE CustomerID = '" + ID + "'";
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
 //------------------------------- News And Events ------------------------------------
     public static Vector InitNewsTableData(JTable tbl) {
        cn = ConnectionDB.Connect();
        Vector header = new Vector();
        Vector data = new Vector();
        header.add("News ID");
        header.add("News Tittle");
//        header.add("Date Post");
//        header.add("News Image");
//        header.add("Status");
        try {
            String sql = "SELECT * FROM NewsEvents";
            st = cn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Vector row = new Vector();
                row.add(rs.getString(1));
                row.add(rs.getString(2));
//                row.add(rs.getString(4));
//                 row.add(rs.getString(5));
//                  row.add(rs.getString(6));
                data.add(row);
            }
            tbl.setModel(SetTableModel(data, header));
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return data;
     }
     public static ResultSet InitContentNews(String ID) {
        cn = ConnectionDB.Connect();
        String sql = "SELECT * FROM NewsEvents WHERE NewsID = '" + ID + "'";
        try {
            st = cn.createStatement();
            rs = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
     public static int AddNews(News news) {
        int executeUpdate = 0;
        cn = ConnectionDB.Connect();
        try {
            String sqlInsert = "INSERT INTO NewsEvents([NewsName],[NewsContent],[DatePost],[NewImage],[Status]) VALUES (?,?,?,?,?)";
            PreparedStatement ps = cn.prepareStatement(sqlInsert);
            ps.setString(1, news.getName());
            ps.setString(2, news.getContent());
            ps.setString(3, news.getDate());
            ps.setString(4, news.getImage());
            ps.setString(5, news.getStatus());
            executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(InitData.class.getName()).log(Level.SEVERE, null, ex);
        }
        return executeUpdate;
    }
}
